/*
 * This class deals with the insertion of records into the database.
 */

package Database;

import java.sql.*;

/**<P>A package which allows a user to insert a record into the cs342group4
 * database.  In doing so, we establish a connection with the database, then
 * execute preparedStatements to insert the record into a given table.</P>
 *
 * @author Kevin Boerboom
 */
public class DBInsert {

    /**Used to obtain the connection with the cs342group4 database
     */
    public static Connection conn;

    /**Inserts a record into the Connections table
     * @param connID, timeStamp, IPAddress, serverID
     * @return true if there is a successful insertion
     */
    public static boolean insertIntoConnections(String connID, String timeStamp, String IPAddress, String serverID){
        String insertRec = "INSERT INTO Connections(connID,timeStamp,IPAddress,serverID) VALUES (?,?,?,?)";
        PreparedStatement ps = null;
        conn = DBConnection.getConn();
        try{
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(insertRec);
            ps.setInt(1, Integer.parseInt(connID));
            ps.setString(2, timeStamp);
            ps.setString(3, IPAddress);
            ps.setString(4, serverID);
            //System.out.println("Attempting insert");
            ps.executeUpdate();
            conn.commit();
            //System.out.println("Insert successful");
            return true;
        }catch(SQLException e){
            System.out.println("Unable to insert Connections record");
            e.printStackTrace();
            return false;
        }
    }

    /**Inserts a record into the Visualization table
     * @param connID, serverID, timeStamp, userName, visName
     * @return true if there is a successful insertion
     */
    public static boolean insertIntoVisualization(String connID, String serverID, String timeStamp, String userName,String visName){
        String selectIP = "SELECT IPAddress FROM Connections WHERE connID = ? AND serverID = ? AND timeStamp < ?";
        String insertRec = "INSERT INTO Visualization(connID,serverID, timeStamp,username,visualizationName, country, city, IPAddress) VALUES (?,?,?,?,?,?,?,?)";
        PreparedStatement ps = null;
        conn = DBConnection.getConn();
        String ipAddress = null;
        try{
            ps = conn.prepareStatement(selectIP);
            ps.setString(1, connID);
            ps.setString(2, serverID);
            ps.setString(3, timeStamp);
            ResultSet rs = ps.executeQuery();
            if(rs.last()) {
                ipAddress = rs.getString(1);
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (ipAddress != null) {
            IPAddressInfo ipInfo = new IPAddressInfo(ipAddress);
            try {
                conn.setAutoCommit(false);
                ps = conn.prepareStatement(insertRec);
                ps.setInt(1, Integer.parseInt(connID));
                ps.setString(2, serverID);
                ps.setString(3, timeStamp);
                ps.setString(4, userName);
                ps.setString(5, visName);
                ps.setString(6, ipInfo.getCountry());
                ps.setString(7, ipInfo.getCity());
                ps.setString(8, ipAddress);
                System.out.println("Attempting insert");
                ps.executeUpdate();
                conn.commit();
                System.out.println("Insert successful");
                return true;
            } catch (SQLException e) {
                System.out.println("Unable to insert Visualization record");
                e.printStackTrace();
                return false;
            }
        }
        return false;
    }
}
